/*
 * DespachoDAO.java
 *
 * Created on 13 de septiembre de 2007, 10:17 AM
 *
 * To change this template, choose Tools | Options and locate the template under
 * the Source Creation and Management node. Right-click the template and choose
 * Open. You can then make changes to the template in the Source Editor.
 */

package com.src.computec.radar.despachos.despacho.dao;

import com.src.computec.radar.base.dao.GenericDAO;
import com.src.computec.radar.base.dao.exception.DAOException;
import com.src.computec.radar.despachos.base.constantes.ConstantesGenerales;
import static com.src.computec.radar.despachos.base.constantes.ConstantesGenerales.SCHEMA;
import com.src.computec.radar.despachos.caja.dto.CajaDto;
import com.src.computec.radar.despachos.caja.dto.CajaMovimientoDto;
import com.src.computec.radar.despachos.caja.exception.CajaException;
import com.src.computec.radar.despachos.despacho.dto.BusquedaDto;
import com.src.computec.radar.despachos.despacho.dto.DespachoDto;
import com.src.computec.radar.despachos.despacho.dto.DespachoMovimientoDto;
import com.src.computec.radar.despachos.despacho.dto.DetalleCajaDespachadaDto;
import com.src.computec.radar.despachos.despacho.dto.OrdenProcesoPorDespacharDto;
import com.src.computec.radar.despachos.despacho.exception.DespachoException;
import com.src.computec.radar.despachos.source.DespachosSource;
import com.src.computec.radar.services.utilities.dto.EnvioDto;
import com.src.computec.radar.services.utilities.dto.EnvioMovimientoDto;
import com.src.computec.radar.services.utilities.dto.OrdenProcesoDto;
import com.src.log4j.logger.custom.XLogger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 *
 * @author aocampo
 */
public class DespachoDAO extends GenericDAO implements BaseDespachoDAO {
    
    private static XLogger xLogger;
    
    /** Creates a new instance of DespachoDAO */
    public DespachoDAO() {
        this.xLogger = (XLogger) XLogger.getLogger(this.getClass().getName());
    }
    
    public DespachoDto getDespachoByPrimaryKey(DespachoDto dto) throws DAOException {
        DespachoDto despachoDto = null;
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = getConnectionDB2();
            con.setReadOnly(true);
            statement = con.prepareStatement("SELECT D.CODIGO_DESPACHO, D.NUMERO_DESPACHO, D.CODIGO_DEPARTAMENTO, D.CODIGO_MUNICIPIO, D.CODIGO_CENTRO_POBLADO, D.CODIGO_PROVEEDOR, D.CODIGO_ESTADO, D.FECHA_CREACION, D.FECHA_DESPACHO, D.NRO_ENVIOS, DP.NOMBRE_DEPARTAMENTO, CP.NOMBRE_CENTRO_POBLADO, PV.RAZON_SOCIAL, PVD.RAZON_SOCIAL, ED.NOMBRE_ESTADO " +
                                             "FROM " + SCHEMA + ".DESPACHO D " +
                                             "INNER JOIN " + SCHEMA + ".DEPARTAMENTO DP ON D.CODIGO_DEPARTAMENTO = DP.CODIGO_DEPARTAMENTO " +
                                             "INNER JOIN " + SCHEMA + ".MUNICIPIO M ON D.CODIGO_DEPARTAMENTO = M.CODIGO_DEPARTAMENTO AND D.CODIGO_MUNICIPIO = M.CODIGO_MUNICIPIO " +
                                             "INNER JOIN " + SCHEMA + ".CENTRO_POBLADO CP ON D.CODIGO_DEPARTAMENTO = CP.CODIGO_DEPARTAMENTO AND D.CODIGO_MUNICIPIO = CP.CODIGO_MUNICIPIO AND D.CODIGO_CENTRO_POBLADO = CP.CODIGO_CENTROP " +
                                             "INNER JOIN " + SCHEMA + ".PROVEEDOR PV ON D.CODIGO_PROVEEDOR = PV.CODIGO_PROVEEDOR " +
                                             "INNER JOIN " + SCHEMA + ".PROVEEDOR PVD ON D.CODIGO_PROVEEDOR_DESPACHADO = PVD.CODIGO_PROVEEDOR " +
                                             "INNER JOIN " + SCHEMA + ".ESTADO_DESPACHO ED ON D.CODIGO_ESTADO = ED.CODIGO_ESTADO " +
                                             "WHERE D.CODIGO_DESPACHO = ? ");
            int indice = 0;
            statement.setObject(++indice, dto.getCodigoDespacho());
            rs = statement.executeQuery();
            if (rs.next()) {
                indice = 0;
                despachoDto = new DespachoDto();
                despachoDto.setCodigoDespacho(rs.getString(++indice));
                despachoDto.setNumeroDespacho(rs.getString(++indice));
                despachoDto.setCodigoDepartamento(rs.getString(++indice));
                despachoDto.setCodigoMunicipio(rs.getString(++indice));
                despachoDto.setCodigoCentroPoblado(rs.getString(++indice));
                despachoDto.setCodigoProveedor(rs.getString(++indice));
                despachoDto.setCodigoEstado(rs.getString(++indice));
                despachoDto.setFechaCreacion(new Date(rs.getTimestamp(++indice).getTime()));
                despachoDto.setFechaCreacionFormat(ConstantesGenerales.SIMPLE_DATE_FORMAT.format(despachoDto.getFechaCreacion()));
                Timestamp fechaDespacho = rs.getTimestamp(++indice);
                if (fechaDespacho != null) {
                    despachoDto.setFechaDespacho(new Date(fechaDespacho.getTime()));
                    despachoDto.setFechaDespachoFormat(ConstantesGenerales.SIMPLE_DATE_FORMAT.format(despachoDto.getFechaDespacho()));
                }
                else {
                    despachoDto.setFechaDespachoFormat("");
                }
                despachoDto.setNumeroEnvios(rs.getInt(++indice));
                despachoDto.setNombreDepartamento(rs.getString(++indice));
                despachoDto.setNombreCentroPoblado(rs.getString(++indice));
                despachoDto.setRazonSocialProveedor(rs.getString(++indice));
                despachoDto.setRazonSocialProveedorDespachado(rs.getString(++indice));
                despachoDto.setNombreEstado(rs.getString(++indice));
                String codigoDane = despachoDto.getCodigoDepartamento() + despachoDto.getCodigoMunicipio() + despachoDto.getCodigoCentroPoblado();
                if (despachoDto.getCodigoEstado().equals(ConstantesGenerales.CODIGO_ESTADO_DESPACHO_ABIERTO)) {
                    despachoDto.setAbierto(true);
                }
                else {
                    despachoDto.setAbierto(false);
                }
                if (codigoDane.equals(DespachosSource.getInstance().getDespachosSetupKey("codigoDaneBogota"))) {
                    despachoDto.setBogota(true);
                }
                else {
                    despachoDto.setBogota(false);
                }
            }
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:getDespachoByPrimaryKey:" + e.toString(), e);
        }
        finally {
            closeConnection(con, statement, rs);
        }
        return despachoDto;
    }
    
    public List getDespachosByBusqueda(BusquedaDto dto) throws DAOException {
        List listObjects = new ArrayList();
        List listDespachos = new ArrayList();
        String baseQuery = "SELECT D.CODIGO_DESPACHO, D.NUMERO_DESPACHO, D.CODIGO_DEPARTAMENTO, D.CODIGO_MUNICIPIO, D.CODIGO_CENTRO_POBLADO, D.CODIGO_PROVEEDOR, D.CODIGO_ESTADO, D.FECHA_CREACION, D.FECHA_DESPACHO, D.NRO_ENVIOS, DP.NOMBRE_DEPARTAMENTO, CP.NOMBRE_CENTRO_POBLADO, PV.RAZON_SOCIAL, PVD.RAZON_SOCIAL, ED.NOMBRE_ESTADO " +
                           "FROM " + SCHEMA + ".DESPACHO D " +
                           "INNER JOIN " + SCHEMA + ".DEPARTAMENTO DP ON D.CODIGO_DEPARTAMENTO = DP.CODIGO_DEPARTAMENTO " +
                           "INNER JOIN " + SCHEMA + ".MUNICIPIO M ON D.CODIGO_DEPARTAMENTO = M.CODIGO_DEPARTAMENTO AND D.CODIGO_MUNICIPIO = M.CODIGO_MUNICIPIO " +
                           "INNER JOIN " + SCHEMA + ".CENTRO_POBLADO CP ON D.CODIGO_DEPARTAMENTO = CP.CODIGO_DEPARTAMENTO AND D.CODIGO_MUNICIPIO = CP.CODIGO_MUNICIPIO AND D.CODIGO_CENTRO_POBLADO = CP.CODIGO_CENTROP " +
                           "INNER JOIN " + SCHEMA + ".PROVEEDOR PV ON D.CODIGO_PROVEEDOR = PV.CODIGO_PROVEEDOR " +
                           "INNER JOIN " + SCHEMA + ".PROVEEDOR PVD ON D.CODIGO_PROVEEDOR_DESPACHADO = PVD.CODIGO_PROVEEDOR " +
                           "INNER JOIN " + SCHEMA + ".ESTADO_DESPACHO ED ON D.CODIGO_ESTADO = ED.CODIGO_ESTADO ";
        String baseWhere = "";
        String baseOrderBy = " ORDER BY PV.RAZON_SOCIAL ASC WITH UR";
        String codigoTipoBusqueda = dto.getCodigoTipoBusqueda();
        if (dto.isEmpresaMensajeria()) {
            baseWhere = "WHERE D.CODIGO_PROVEEDOR = ? AND D.CODIGO_ESTADO <> ?";
            listObjects.add(dto.getCodigoProveedor());
            listObjects.add(ConstantesGenerales.CODIGO_ESTADO_DESPACHO_ABIERTO);
            if (codigoTipoBusqueda.equals(BusquedaDto.CODIGO_BUSQUEDA_BASICA)) {
                String busqueda = dto.getBusqueda();
                if (busqueda != null && !busqueda.equals("")) {
                    busqueda = busqueda.toUpperCase();
                    baseWhere += " AND (CAST(D.CODIGO_DESPACHO AS CHAR(8)) LIKE '%" + busqueda + "%' OR CAST(D.NUMERO_DESPACHO AS CHAR(8)) LIKE '%" + busqueda + "%' OR DP.NOMBRE_DEPARTAMENTO LIKE '%" + busqueda + "%' OR CP.NOMBRE_CENTRO_POBLADO LIKE '%" + busqueda + "%' OR PV.RAZON_SOCIAL LIKE '%" + busqueda + "%' OR PVD.RAZON_SOCIAL LIKE '%" + busqueda + "%' OR ED.NOMBRE_ESTADO LIKE '%" + busqueda + "%')";
                }
            }
            else {
                String codigoEstado = dto.getCodigoEstado();
                Date fechaInicio = dto.getFechaInicio();
                Date fechaFin = dto.getFechaFin();
                if (!codigoEstado.equals("")) {
                    baseWhere += " AND D.CODIGO_ESTADO = ?";
                    listObjects.add(codigoEstado);
                }
                if (fechaInicio != null && fechaFin != null) {
                    Calendar calendarFechaInicio = Calendar.getInstance();
                    calendarFechaInicio.setTime(dto.getFechaInicio());
                    calendarFechaInicio.set(Calendar.HOUR_OF_DAY, 0);
                    calendarFechaInicio.set(Calendar.MINUTE, 0);
                    calendarFechaInicio.set(Calendar.SECOND, 0);
                    calendarFechaInicio.set(Calendar.MILLISECOND, 0);
                    Calendar calendarFechaFin = Calendar.getInstance();
                    calendarFechaFin.setTime(dto.getFechaFin());
                    calendarFechaFin.set(Calendar.HOUR_OF_DAY, 23);
                    calendarFechaFin.set(Calendar.MINUTE, 59);
                    calendarFechaFin.set(Calendar.SECOND, 59);
                    calendarFechaFin.set(Calendar.MILLISECOND, 59);
                    baseWhere += " AND D.FECHA_DESPACHO BETWEEN ? AND ?";
                    listObjects.add(calendarFechaInicio.getTime());
                    listObjects.add(calendarFechaFin.getTime());
                }
            }
        }
        else if (dto.isOperadorDespachos()) {
            if (codigoTipoBusqueda.equals(BusquedaDto.CODIGO_BUSQUEDA_BASICA)) {
                String busqueda = dto.getBusqueda();
                if (busqueda != null && !busqueda.equals("")) {
                    busqueda = busqueda.toUpperCase();
                    baseWhere += "WHERE (CAST(D.CODIGO_DESPACHO AS CHAR(8)) LIKE '%" + busqueda + "%' OR CAST(D.NUMERO_DESPACHO AS CHAR(8)) LIKE '%" + busqueda + "%' OR DP.NOMBRE_DEPARTAMENTO LIKE '%" + busqueda + "%' OR CP.NOMBRE_CENTRO_POBLADO LIKE '%" + busqueda + "%' OR PV.RAZON_SOCIAL LIKE '%" + busqueda + "%' OR PVD.RAZON_SOCIAL LIKE '%" + busqueda + "%' OR ED.NOMBRE_ESTADO LIKE '%" + busqueda + "%') AND D.CODIGO_ESTADO = ?";
                    listObjects.add(ConstantesGenerales.CODIGO_ESTADO_DESPACHO_ABIERTO);
                }
                else {
                    baseWhere += "WHERE D.CODIGO_ESTADO = ?";
                    listObjects.add(ConstantesGenerales.CODIGO_ESTADO_DESPACHO_ABIERTO);
                }
            }
            else {
                String codigoProveedor = dto.getCodigoProveedor();
                String codigoProveedorDespachado = dto.getCodigoProveedorDespachado();
                String codigoEstado = dto.getCodigoEstado();
                String codigoDepartamento = dto.getCodigoDepartamento();
                String codigoMunicipio = dto.getCodigoMunicipio();
                String codigoCentroPoblado = dto.getCodigoCentroPoblado();
                Date fechaInicio = dto.getFechaInicio();
                Date fechaFin = dto.getFechaFin();
                boolean useFilter = false;
                if (!codigoProveedor.equals("")) {
                    if (!useFilter) {
                        baseWhere += "WHERE D.CODIGO_PROVEEDOR = ?";
                        useFilter = true;
                    }
                    else {
                        baseWhere += " AND D.CODIGO_PROVEEDOR = ?";
                    }
                    listObjects.add(codigoProveedor);
                }
                if (!codigoProveedorDespachado.equals("")) {
                    if (!useFilter) {
                        baseWhere += "WHERE D.CODIGO_PROVEEDOR_DESPACHADO = ?";
                        useFilter = true;
                    }
                    else {
                        baseWhere += " AND D.CODIGO_PROVEEDOR_DESPACHADO = ?";
                    }
                    listObjects.add(codigoProveedorDespachado);
                }
                if (!codigoEstado.equals("")) {
                    if (!useFilter) {
                        baseWhere += "WHERE D.CODIGO_ESTADO = ?";
                        useFilter = true;
                    }
                    else {
                        baseWhere += " AND D.CODIGO_ESTADO = ?";
                    }
                    listObjects.add(codigoEstado);
                }
                if (!codigoDepartamento.equals("")) {
                    if (!useFilter) {
                        baseWhere += "WHERE D.CODIGO_DEPARTAMENTO = ?";
                        useFilter = true;
                    }
                    else {
                        baseWhere += " AND D.CODIGO_DEPARTAMENTO = ?";
                    }
                    listObjects.add(codigoDepartamento);
                }
                if (!codigoMunicipio.equals("")) {
                    if (!useFilter) {
                        baseWhere += "WHERE D.CODIGO_MUNICIPIO = ?";
                        useFilter = true;
                    }
                    else {
                        baseWhere += " AND D.CODIGO_MUNICIPIO = ?";
                    }
                    listObjects.add(codigoMunicipio);
                }
                if (!codigoCentroPoblado.equals("")) {
                    if (!useFilter) {
                        baseWhere += "WHERE D.CODIGO_CENTRO_POBLADO = ?";
                        useFilter = true;
                    }
                    else {
                        baseWhere += " AND D.CODIGO_CENTRO_POBLADO = ?";
                    }
                    listObjects.add(codigoCentroPoblado);
                }
                if (fechaInicio != null && fechaFin != null) {
                    Calendar calendarFechaInicio = Calendar.getInstance();
                    calendarFechaInicio.setTime(dto.getFechaInicio());
                    calendarFechaInicio.set(Calendar.HOUR_OF_DAY, 0);
                    calendarFechaInicio.set(Calendar.MINUTE, 0);
                    calendarFechaInicio.set(Calendar.SECOND, 0);
                    calendarFechaInicio.set(Calendar.MILLISECOND, 0);
                    Calendar calendarFechaFin = Calendar.getInstance();
                    calendarFechaFin.setTime(dto.getFechaFin());
                    calendarFechaFin.set(Calendar.HOUR_OF_DAY, 23);
                    calendarFechaFin.set(Calendar.MINUTE, 59);
                    calendarFechaFin.set(Calendar.SECOND, 59);
                    calendarFechaFin.set(Calendar.MILLISECOND, 59);
                    if (!useFilter) {
                        baseWhere += "WHERE D.FECHA_DESPACHO BETWEEN ? AND ?";
                    }
                    else {
                        baseWhere += " AND D.FECHA_DESPACHO BETWEEN ? AND ?";
                    }
                    listObjects.add(calendarFechaInicio.getTime());
                    listObjects.add(calendarFechaFin.getTime());
                }
            }
        }
        else {
            if (codigoTipoBusqueda.equals(BusquedaDto.CODIGO_BUSQUEDA_BASICA)) {
                String busqueda = dto.getBusqueda();
                if (busqueda != null && !busqueda.equals("")) {
                    busqueda = busqueda.toUpperCase();
                    baseWhere = "WHERE CAST(D.CODIGO_DESPACHO AS CHAR(8)) LIKE '%" + busqueda + "%' OR CAST(D.NUMERO_DESPACHO AS CHAR(8)) LIKE '%" + busqueda + "%' OR DP.NOMBRE_DEPARTAMENTO LIKE '%" + busqueda + "%' OR CP.NOMBRE_CENTRO_POBLADO LIKE '%" + busqueda + "%' OR PV.RAZON_SOCIAL LIKE '%" + busqueda + "%' OR PVD.RAZON_SOCIAL LIKE '%" + busqueda + "%' OR ED.NOMBRE_ESTADO LIKE '%" + busqueda + "%'";
                }
            }
            else {
                String codigoProveedor = dto.getCodigoProveedor();
                String codigoProveedorDespachado = dto.getCodigoProveedorDespachado();
                String codigoEstado = dto.getCodigoEstado();
                String codigoDepartamento = dto.getCodigoDepartamento();
                String codigoMunicipio = dto.getCodigoMunicipio();
                String codigoCentroPoblado = dto.getCodigoCentroPoblado();
                Date fechaInicio = dto.getFechaInicio();
                Date fechaFin = dto.getFechaFin();
                boolean useFilter = false;
                if (!codigoProveedor.equals("")) {
                    if (!useFilter) {
                        baseWhere += "WHERE D.CODIGO_PROVEEDOR = ?";
                        useFilter = true;
                    }
                    else {
                        baseWhere += " AND D.CODIGO_PROVEEDOR = ?";
                    }
                    listObjects.add(codigoProveedor);
                }
                if (!codigoProveedorDespachado.equals("")) {
                    if (!useFilter) {
                        baseWhere += "WHERE D.CODIGO_PROVEEDOR_DESPACHADO = ?";
                        useFilter = true;
                    }
                    else {
                        baseWhere += " AND D.CODIGO_PROVEEDOR_DESPACHADO = ?";
                    }
                    listObjects.add(codigoProveedorDespachado);
                }
                if (!codigoEstado.equals("")) {
                    if (!useFilter) {
                        baseWhere += "WHERE D.CODIGO_ESTADO = ?";
                        useFilter = true;
                    }
                    else {
                        baseWhere += " AND D.CODIGO_ESTADO = ?";
                    }
                    listObjects.add(codigoEstado);
                }
                if (!codigoDepartamento.equals("")) {
                    if (!useFilter) {
                        baseWhere += "WHERE D.CODIGO_DEPARTAMENTO = ?";
                        useFilter = true;
                    }
                    else {
                        baseWhere += " AND D.CODIGO_DEPARTAMENTO = ?";
                    }
                    listObjects.add(codigoDepartamento);
                }
                if (!codigoMunicipio.equals("")) {
                    if (!useFilter) {
                        baseWhere += "WHERE D.CODIGO_MUNICIPIO = ?";
                        useFilter = true;
                    }
                    else {
                        baseWhere += " AND D.CODIGO_MUNICIPIO = ?";
                    }
                    listObjects.add(codigoMunicipio);
                }
                if (!codigoCentroPoblado.equals("")) {
                    if (!useFilter) {
                        baseWhere += "WHERE D.CODIGO_CENTRO_POBLADO = ?";
                        useFilter = true;
                    }
                    else {
                        baseWhere += " AND D.CODIGO_CENTRO_POBLADO = ?";
                    }
                    listObjects.add(codigoCentroPoblado);
                }
                if (fechaInicio != null && fechaFin != null) {
                    Calendar calendarFechaInicio = Calendar.getInstance();
                    calendarFechaInicio.setTime(dto.getFechaInicio());
                    calendarFechaInicio.set(Calendar.HOUR_OF_DAY, 0);
                    calendarFechaInicio.set(Calendar.MINUTE, 0);
                    calendarFechaInicio.set(Calendar.SECOND, 0);
                    calendarFechaInicio.set(Calendar.MILLISECOND, 0);
                    Calendar calendarFechaFin = Calendar.getInstance();
                    calendarFechaFin.setTime(dto.getFechaFin());
                    calendarFechaFin.set(Calendar.HOUR_OF_DAY, 23);
                    calendarFechaFin.set(Calendar.MINUTE, 59);
                    calendarFechaFin.set(Calendar.SECOND, 59);
                    calendarFechaFin.set(Calendar.MILLISECOND, 59);
                    if (!useFilter) {
                        baseWhere += "WHERE D.FECHA_DESPACHO BETWEEN ? AND ?";
                    }
                    else {
                        baseWhere += " AND D.FECHA_DESPACHO BETWEEN ? AND ?";
                    }
                    listObjects.add(calendarFechaInicio.getTime());
                    listObjects.add(calendarFechaFin.getTime());
                }
            }
        }
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = getConnectionDB2();
            con.setReadOnly(true);
            statement = con.prepareStatement(baseQuery + baseWhere + baseOrderBy);
            int indice = 0;
            Iterator iterator = listObjects.iterator();
            while (iterator.hasNext()) {
                Object object = iterator.next();
                if (object instanceof String) {
                    statement.setObject(++indice, (String) object);
                }
                else {
                    statement.setObject(++indice, new Timestamp(((Date) object).getTime()));
                }
            }
            rs = statement.executeQuery();
            while (rs.next()) {
                indice = 0;
                DespachoDto despachoDto = new DespachoDto();
                despachoDto.setCodigoDespacho(rs.getString(++indice));
                despachoDto.setNumeroDespacho(rs.getString(++indice));
                despachoDto.setCodigoDepartamento(rs.getString(++indice));
                despachoDto.setCodigoMunicipio(rs.getString(++indice));
                despachoDto.setCodigoCentroPoblado(rs.getString(++indice));
                despachoDto.setCodigoProveedor(rs.getString(++indice));
                despachoDto.setCodigoEstado(rs.getString(++indice));
                despachoDto.setFechaCreacion(new Date(rs.getTimestamp(++indice).getTime()));
                despachoDto.setFechaCreacionFormat(ConstantesGenerales.SIMPLE_DATE_FORMAT.format(despachoDto.getFechaCreacion()));
                Timestamp fechaDespacho = rs.getTimestamp(++indice);
                if (fechaDespacho != null) {
                    despachoDto.setFechaDespacho(new Date(fechaDespacho.getTime()));
                    despachoDto.setFechaDespachoFormat(ConstantesGenerales.SIMPLE_DATE_FORMAT.format(despachoDto.getFechaDespacho()));
                }
                else {
                    despachoDto.setFechaDespachoFormat("");
                }
                despachoDto.setNumeroEnvios(rs.getInt(++indice));
                despachoDto.setNombreDepartamento(rs.getString(++indice));
                despachoDto.setNombreCentroPoblado(rs.getString(++indice));
                despachoDto.setRazonSocialProveedor(rs.getString(++indice));
                despachoDto.setRazonSocialProveedorDespachado(rs.getString(++indice));
                despachoDto.setNombreEstado(rs.getString(++indice));
                String codigoDane = despachoDto.getCodigoDepartamento() + despachoDto.getCodigoMunicipio() + despachoDto.getCodigoCentroPoblado();
                if (despachoDto.getCodigoEstado().equals(ConstantesGenerales.CODIGO_ESTADO_DESPACHO_ABIERTO)) {
                    despachoDto.setAbierto(true);
                }
                else {
                    despachoDto.setAbierto(false);
                }
                if (codigoDane.equals(DespachosSource.getInstance().getDespachosSetupKey("codigoDaneBogota"))) {
                    despachoDto.setBogota(true);
                }
                else {
                    despachoDto.setBogota(false);
                }
                listDespachos.add(despachoDto);
            }
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:getDespachosByBusqueda:" + e.toString(), e);
        }
        finally {
            closeConnection(con, statement, rs);
        }
        return listDespachos;
    }
    
    public List getOrdenProcesoPorDespachar(String numeroOrden) throws DAOException {
        List listOrdenesProcesoPorDespachar = new ArrayList();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        Date today = new Date();
        try {
            con = getConnectionDB2();
            con.setReadOnly(true);
            statement = con.prepareStatement("SELECT E.NUMERO_ORDEN, C.RAZON_SOCIAL, P.DESCRIPCION_PRODUCTO, E.FECHA_PROCESO, COUNT(E.CODIGO_BARRAS) AS NUMERO_ENVIOS " +
                                             "FROM PRODRAD.ENVIO E " +
                                             "INNER JOIN PRODRAD.CLIENTE C ON E.CODIGO_CLIENTE = C.CODIGO_CLIENTE " +
                                             "INNER JOIN PRODRAD.PRODUCTO P ON E.CODIGO_CLIENTE = P.CODIGO_CLIENTE AND E.CODIGO_PRODUCTO = P.CODIGO_PRODUCTO " +
                                             "WHERE E.NUMERO_ORDEN = ? AND E.CODIGO_CAJA IS NULL AND E.CODIGO_ULTIMA_GESTION IN (?, ?) " +
                                             "GROUP BY E.NUMERO_ORDEN, C.RAZON_SOCIAL, P.DESCRIPCION_PRODUCTO, E.FECHA_PROCESO WITH UR");
            int indice = 0;
            statement.setObject(++indice, numeroOrden);
            statement.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_DISTRIBUCION_EXTERNA);
            statement.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_ALISTAMIENTO);
            rs = statement.executeQuery();
            while (rs.next()) {
                indice = 0;
                OrdenProcesoDto ordenProcesoDto = new OrdenProcesoDto();
                ordenProcesoDto.setNumeroOrden(rs.getString(++indice));
                ordenProcesoDto.setRazonSocialCliente(rs.getString(++indice));
                ordenProcesoDto.setDescripcionProducto(rs.getString(++indice));
                ordenProcesoDto.setFechaProceso(rs.getDate(++indice));
                ordenProcesoDto.setFechaProcesoFormat(ConstantesGenerales.SHORT_SIMPLE_DATE_FORMAT.format(ordenProcesoDto.getFechaProceso()));
                ordenProcesoDto.setNumeroEnvios(rs.getString(++indice));
                listOrdenesProcesoPorDespachar.add(ordenProcesoDto);
            }
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:getOrdenProcesoPorDespachar:" + e.toString(), e);
        }
        finally {
            closeConnection(con, statement, rs);
        }
        return listOrdenesProcesoPorDespachar;
    }
    
    // <editor-fold defaultstate="collapsed" desc="method disabled: getOrdenesProcesoPorDespachar()">
    /*public List getOrdenesProcesoPorDespachar(String daysBackStr) throws DAOException {
        List listOrdenesProcesoPorDespachar = new ArrayList();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        Date today = new Date();
        try {
            con = getConnectionDB2();
            con.setReadOnly(true);
            statement = con.prepareStatement("SELECT E.NUMERO_ORDEN, C.RAZON_SOCIAL, P.DESCRIPCION_PRODUCTO, OP.CICLO, OP.FECHA_PROCESO, COUNT(E.CODIGO_BARRAS) AS NUMERO_ENVIOS " +
                                             "FROM " + SCHEMA + ".ENVIO E " +
                                             "INNER JOIN " + SCHEMA + ".CLIENTE C ON E.CODIGO_CLIENTE = C.CODIGO_CLIENTE " +
                                             "INNER JOIN " + SCHEMA + ".PRODUCTO P ON E.CODIGO_CLIENTE = P.CODIGO_CLIENTE AND E.CODIGO_PRODUCTO = P.CODIGO_PRODUCTO " +
                                             "INNER JOIN " + SCHEMA + ".ORDEN_PROCESO OP ON E.NUMERO_ORDEN = OP.NUMERO_ORDEN " +
                                             "WHERE OP.FECHA_PROCESO BETWEEN ? AND ? AND E.CODIGO_CAJA IS NULL AND E.CODIGO_ULTIMA_GESTION IN (?, ?) " + //CAMBIO GESTION
                                             "GROUP BY E.NUMERO_ORDEN, C.RAZON_SOCIAL, P.DESCRIPCION_PRODUCTO, OP.CICLO, OP.FECHA_PROCESO " +
                                             "ORDER BY OP.FECHA_PROCESO ASC, NUMERO_ENVIOS DESC WITH UR");
            int indice = 0;
            //int daysBack = Integer.valueOf(DespachosSource.getInstance().getDespachosSetupKey("daysBackDefault")).intValue();
            int daysBack = Integer.valueOf(daysBackStr).intValue();
            Calendar calendarFechaInicio = Calendar.getInstance();
            calendarFechaInicio.setTime(today);
            calendarFechaInicio.add(Calendar.DATE, -daysBack);
            calendarFechaInicio.set(Calendar.HOUR_OF_DAY, 0);
            calendarFechaInicio.set(Calendar.MINUTE, 0);
            calendarFechaInicio.set(Calendar.SECOND, 0);
            calendarFechaInicio.set(Calendar.MILLISECOND, 0);
            statement.setDate(++indice, new java.sql.Date(calendarFechaInicio.getTimeInMillis()));
            statement.setDate(++indice, new java.sql.Date(today.getTime()));
            statement.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_DESPACHO);
            statement.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_DISTRIBUCION_EXTERNA);
            rs = statement.executeQuery();
            while (rs.next()) {
                indice = 0;
                OrdenProcesoDto ordenProcesoDto = new OrdenProcesoDto();
                ordenProcesoDto.setNumeroOrden(rs.getString(++indice));
                ordenProcesoDto.setRazonSocialCliente(rs.getString(++indice));
                ordenProcesoDto.setDescripcionProducto(rs.getString(++indice));
                ordenProcesoDto.setCiclo(rs.getString(++indice));
                ordenProcesoDto.setFechaProceso(rs.getDate(++indice));
                ordenProcesoDto.setFechaProcesoFormat(ConstantesGenerales.SHORT_SIMPLE_DATE_FORMAT.format(ordenProcesoDto.getFechaProceso()));
                ordenProcesoDto.setNumeroEnvios(rs.getString(++indice));
                listOrdenesProcesoPorDespachar.add(ordenProcesoDto);
            }
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:getOrdenesProcesoPorDespachar:" + e.toString(), e);
        }
        finally {
            closeConnection(con, statement, rs);
        }
        return listOrdenesProcesoPorDespachar;
    }*/
    // </editor-fold>
    
    private boolean hasDestinoProveedorDireccionDespachoAbierto(Connection con, String codigoDepartamento, String codigoMunicipio, String codigoCentroPoblado, String codigoProveedor, String direccion) throws DAOException, DespachoException {
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            statement = con.prepareStatement("SELECT CODIGO_DESPACHO " +
                                             "FROM " + SCHEMA + ".DESPACHO " +
                                             "WHERE CODIGO_DEPARTAMENTO = ? AND CODIGO_MUNICIPIO = ? AND CODIGO_CENTRO_POBLADO = ? AND CODIGO_PROVEEDOR = ? AND DIRECCION = ? AND CODIGO_ESTADO = ?");
            int indice = 0;
            statement.setObject(++indice, codigoDepartamento);
            statement.setObject(++indice, codigoMunicipio);
            statement.setObject(++indice, codigoCentroPoblado);
            statement.setObject(++indice, codigoProveedor);
            statement.setObject(++indice, direccion);
            statement.setObject(++indice, ConstantesGenerales.CODIGO_ESTADO_DESPACHO_ABIERTO);
            rs = statement.executeQuery();
            if (rs.next()) {
                return true;
            }
            else {
                return false;
            }
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:hasDestinoProveedorDireccionDespachoAbierto:" + e.toString(), e);
        }
        finally {
            closeConnection(null, statement, rs);
        }
    }
    
    public List getDetalleOrdenProcesoPorDespachar(OrdenProcesoDto dto) throws DAOException {
        List listDetalleOrdenProcesoPorDespachar = new ArrayList();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = getConnectionDB2();
            con.setReadOnly(true);
            statement = con.prepareStatement("SELECT ND.CODIGO_NODO, ND.DIRECCION, ND.CODIGO_DEPARTAMENTO, ND.CODIGO_MUNICIPIO, ND.CODIGO_CENTROP, E.NUMERO_ORDEN, E.FECHA_PROCESO, E.CODIGO_CLIENTE, E.CODIGO_PRODUCTO, ND.CODIGO_PROVEEDOR, ND.NOMBRE_NODO, D.NOMBRE_DEPARTAMENTO, CP.NOMBRE_CENTRO_POBLADO, C.RAZON_SOCIAL, P.DESCRIPCION_PRODUCTO, PV.RAZON_SOCIAL, COUNT(E.CODIGO_BARRAS) AS CANTIDAD " +
                                             "FROM " + SCHEMA + ".ENVIO E " +
                                             "INNER JOIN " + SCHEMA + ".NODO_DISTRIBUCION ND ON E.CODIGO_NODO = ND.CODIGO_NODO " +
                                             "INNER JOIN " + SCHEMA + ".DEPARTAMENTO D ON ND.CODIGO_DEPARTAMENTO = D.CODIGO_DEPARTAMENTO " +
                                             "INNER JOIN " + SCHEMA + ".MUNICIPIO M ON ND.CODIGO_DEPARTAMENTO = M.CODIGO_DEPARTAMENTO AND ND.CODIGO_MUNICIPIO = M.CODIGO_MUNICIPIO " +
                                             "INNER JOIN " + SCHEMA + ".CENTRO_POBLADO CP ON ND.CODIGO_DEPARTAMENTO = CP.CODIGO_DEPARTAMENTO AND ND.CODIGO_MUNICIPIO = CP.CODIGO_MUNICIPIO AND ND.CODIGO_CENTROP = CP.CODIGO_CENTROP " +
                                             "INNER JOIN " + SCHEMA + ".CLIENTE C ON E.CODIGO_CLIENTE = C.CODIGO_CLIENTE " +
                                             "INNER JOIN " + SCHEMA + ".PRODUCTO P ON E.CODIGO_CLIENTE = P.CODIGO_CLIENTE AND E.CODIGO_PRODUCTO = P.CODIGO_PRODUCTO " +
                                             "INNER JOIN " + SCHEMA + ".PROVEEDOR PV ON ND.CODIGO_PROVEEDOR = PV.CODIGO_PROVEEDOR " +
                                             "WHERE E.NUMERO_ORDEN = ? AND E.CODIGO_CAJA IS NULL AND E.CODIGO_ULTIMA_GESTION IN (?, ?) " +
                                             "GROUP BY ND.CODIGO_NODO, ND.DIRECCION, ND.CODIGO_DEPARTAMENTO, ND.CODIGO_MUNICIPIO, ND.CODIGO_CENTROP, E.NUMERO_ORDEN, E.FECHA_PROCESO, E.CODIGO_CLIENTE, E.CODIGO_PRODUCTO, ND.CODIGO_PROVEEDOR, ND.NOMBRE_NODO, D.NOMBRE_DEPARTAMENTO, M.NOMBRE_MUNICIPIO, CP.NOMBRE_CENTRO_POBLADO, C.RAZON_SOCIAL, P.DESCRIPCION_PRODUCTO, PV.RAZON_SOCIAL " +
                                             "ORDER BY PV.RAZON_SOCIAL ASC, ND.NOMBRE_NODO ASC WITH UR");
            int indice = 0;
            statement.setObject(++indice, dto.getNumeroOrden());
            statement.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_DISTRIBUCION_EXTERNA);
            statement.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_ALISTAMIENTO);
            rs = statement.executeQuery();
            while (rs.next()) {
                indice = 0;
                OrdenProcesoPorDespacharDto ordenProcesoPorDespacharDto = new OrdenProcesoPorDespacharDto();
                ordenProcesoPorDespacharDto.setCodigoNodo(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setDireccion(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setCodigoDepartamento(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setCodigoMunicipio(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setCodigoCentroPoblado(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setNumeroOrden(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setFechaProceso(new Date(rs.getTimestamp(++indice).getTime()));
                ordenProcesoPorDespacharDto.setFechaProcesoFormat(ConstantesGenerales.SHORT_SIMPLE_DATE_FORMAT_TO_HELP.format(ordenProcesoPorDespacharDto.getFechaProceso()));
                ordenProcesoPorDespacharDto.setCodigoCliente(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setCodigoProducto(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setCodigoProveedor(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setNombreNodo(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setNombreDepartamento(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setNombreCentroPoblado(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setRazonSocialCliente(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setDescripcionProducto(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setRazonSocialProveedor(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setCantidad(rs.getString(++indice));
                ordenProcesoPorDespacharDto.setDestinoDespachoAbierto(this.hasDestinoProveedorDireccionDespachoAbierto(con, ordenProcesoPorDespacharDto.getCodigoDepartamento(), ordenProcesoPorDespacharDto.getCodigoMunicipio(), ordenProcesoPorDespacharDto.getCodigoCentroPoblado(), ordenProcesoPorDespacharDto.getCodigoProveedor(), ordenProcesoPorDespacharDto.getDireccion()));
                listDetalleOrdenProcesoPorDespachar.add(ordenProcesoPorDespacharDto);
            }
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:getDetalleOrdenProcesoPorDespachar:" + e.toString(), e);
        }
        catch (DespachoException e) {
            throw new DAOException("DespachoDAO:getDetalleOrdenProcesoPorDespachar:" + e.toString(), e);
        }
        catch (Exception e) {
            throw new DAOException("DespachoDAO:getDetalleOrdenProcesoPorDespachar:" + e.toString(), e);
        }
        finally {
            closeConnection(con, statement, rs);
        }
        return listDetalleOrdenProcesoPorDespachar;
    }
    
    private void insertDespachoMovimiento(Connection con, DespachoMovimientoDto dto) throws DAOException, DespachoException {
        PreparedStatement statement = null;
        try {
            statement = con.prepareStatement("INSERT INTO " + SCHEMA + ".DESPACHO_MOVIMIENTO (CODIGO_DESPACHO, FECHA_MOVIMIENTO, CODIGO_ESTADO, UID_MOVIMIENTO) " +
                                             "VALUES (?, ?, ?, ?)");
            int indice = 0;
            statement.setObject(++indice, dto.getCodigoDespacho());
            statement.setTimestamp(++indice, new Timestamp(dto.getFechaMovimiento().getTime()));
            statement.setObject(++indice, dto.getCodigoEstado());
            statement.setObject(++indice, dto.getUidMovimiento());
            statement.executeUpdate();
        } 
        catch (SQLException e) {
            throw new DespachoException("DespachoDAO:insertDespachoMovimiento:" + e.toString(), e);
        }
        finally {
            closeConnection(null, statement);
        }
    }
    
    public String insertDespacho(DespachoDto dto, String ldapUser) throws DAOException {
        Connection con = null;
        PreparedStatement statement = null;
        try {
            con = getConnectionDB2();
            con.setAutoCommit(false);
            statement = con.prepareStatement("INSERT INTO " + SCHEMA + ".DESPACHO (CODIGO_DESPACHO, CODIGO_DEPARTAMENTO, CODIGO_MUNICIPIO, CODIGO_CENTRO_POBLADO, CODIGO_PROVEEDOR, CODIGO_PROVEEDOR_DESPACHADO, CODIGO_ESTADO, FECHA_CREACION, DIRECCION) " +
                                             "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
            int indice = 0;
            dto.setCodigoDespacho(this.getNextId(con, SCHEMA + ".DESPACHO", "CODIGO_DESPACHO"));
            statement.setObject(++indice, dto.getCodigoDespacho());
            statement.setObject(++indice, dto.getCodigoDepartamento());
            statement.setObject(++indice, dto.getCodigoMunicipio());
            statement.setObject(++indice, dto.getCodigoCentroPoblado());
            statement.setObject(++indice, dto.getCodigoProveedor());
            statement.setObject(++indice, ConstantesGenerales.CODIGO_PROVEEDOR_NO_ASIGNADO);
            statement.setObject(++indice, ConstantesGenerales.CODIGO_ESTADO_DESPACHO_ABIERTO);
            statement.setTimestamp(++indice, new Timestamp(dto.getFechaCreacion().getTime()));
            statement.setObject(++indice, dto.getDireccion());
            statement.executeUpdate();
            DespachoMovimientoDto despachoMovimientoDto = new DespachoMovimientoDto();
            despachoMovimientoDto.setCodigoDespacho(dto.getCodigoDespacho());
            despachoMovimientoDto.setFechaMovimiento(dto.getFechaCreacion());
            despachoMovimientoDto.setCodigoEstado(ConstantesGenerales.CODIGO_ESTADO_DESPACHO_ABIERTO);
            despachoMovimientoDto.setUidMovimiento(ldapUser);
            this.insertDespachoMovimiento(con, despachoMovimientoDto);
            con.commit();
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:insertDespacho:" + e.toString(), e);
        }
        catch (DespachoException e) {
            try {
                con.rollback();
                throw new DAOException("DespachoDAO:insertDespacho:" + e.toString(), e);
            }
            catch (SQLException ex) {
                throw new DAOException("DespachoDAO:insertDespacho:rollback:" + ex.toString(), ex);
            }
        }
        catch (Exception e) {
            try {
                con.rollback();
                throw new DAOException("DespachoDAO:insertDespacho:" + e.toString(), e);
            }
            catch (SQLException ex) {
                throw new DAOException("DespachoDAO:insertDespacho:rollback:" + ex.toString(), ex);
            }
        }
        finally {
            closeConnection(con, statement);
        }
        xLogger.info("DespachoDAO:insertDespacho:codigoDespacho:" + dto.getCodigoDespacho());
        return dto.getCodigoDespacho();
    }
    
    // <editor-fold defaultstate="collapsed" desc="method disabled: getDespachosSeleccionValidTransfer()">
    /*public List getDespachosSeleccionValidTransfer(DespachoDto dto) throws DAOException {
        List listDespachosSeleccion = new ArrayList();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = getConnectionDB2();
            con.setReadOnly(true);
            statement = con.prepareStatement("SELECT CODIGO_DESPACHO " +
                                             "FROM " + SCHEMA + ".DESPACHO " +
                                             "WHERE CODIGO_DEPARTAMENTO = ? AND CODIGO_MUNICIPIO = ? AND CODIGO_CENTRO_POBLADO = ? AND CODIGO_PROVEEDOR = ? AND CODIGO_DESPACHO <> ? AND CODIGO_ESTADO = ?");
            int indice = 0;
            statement.setObject(++indice, dto.getCodigoDepartamento());
            statement.setObject(++indice, dto.getCodigoMunicipio());
            statement.setObject(++indice, dto.getCodigoCentroPoblado());
            statement.setObject(++indice, dto.getCodigoProveedor());
            statement.setObject(++indice, dto.getCodigoDespacho());
            statement.setObject(++indice, ConstantesGenerales.CODIGO_ESTADO_DESPACHO_ABIERTO);
            rs = statement.executeQuery();
            while (rs.next()) {
                indice = 0;
                DespachoDto despachoDto = new DespachoDto();
                despachoDto.setCodigoDespacho(rs.getString(++indice));
                listDespachosSeleccion.add(despachoDto);
            }
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:getDespachosSeleccionValidTransfer:" + e.toString(), e);
        }
        finally {
            closeConnection(con, statement, rs);
        }
        return listDespachosSeleccion;
    }*/
    // </editor-fold>
    
    public boolean hasDespachar(DespachoDto dto) throws DAOException {
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        boolean despachar = true;
        try {
            con = getConnectionDB2();
            con.setReadOnly(true);
            statement = con.prepareStatement("SELECT CODIGO_CAJA, CODIGO_ESTADO " +
                                             "FROM " + SCHEMA + ".CAJA " +
                                             "WHERE CODIGO_DESPACHO = ?");
            int indice = 0;
            statement.setObject(++indice, dto.getCodigoDespacho());
            rs = statement.executeQuery();
            while (rs.next()) {
                indice = 0;
                String codigoCaja = rs.getString(++indice);
                String codigoEstado = rs.getString(++indice);
                if (codigoEstado.equals(ConstantesGenerales.CODIGO_ESTADO_CAJA_ABIERTA)) {
                    despachar = false;
                }
            }
            return despachar;
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:hasDespachar:" + e.toString(), e);
        }
        finally {
            closeConnection(con, statement, rs);
        }
    }
    
    private void insertEnvioMovimiento(Connection con, EnvioMovimientoDto dto) throws DAOException, DespachoException {
        PreparedStatement statement = null;
        try {
            statement = con.prepareStatement("INSERT INTO " + SCHEMA + ".ENVIO_MOVIMIENTO (CODIGO_BARRAS, CODIGO_GESTION, FECHA_MOVIMIENTO, FECHA_WEB, CODIGO_RESPONSABLE, FECHA_PROCESO) " +
                                             "VALUES (?, ?, ?, ?, ?, (SELECT FECHA_PROCESO FROM " + SCHEMA + ".ENVIO WHERE CODIGO_BARRAS = ?))");
            int indice = 0;
            statement.setObject(++indice, dto.getCodigoBarras());
            statement.setObject(++indice, dto.getCodigoGestion());
            statement.setDate(++indice, new java.sql.Date(dto.getFechaMovimiento().getTime()));
            statement.setDate(++indice, new java.sql.Date(dto.getFechaWeb().getTime()));
            statement.setObject(++indice, dto.getCodigoResponsable());
            statement.setObject(++indice, dto.getCodigoBarras());
            statement.executeUpdate();
        } 
        catch (SQLException e) {
            throw new DespachoException("DespachoDAO:insertEnvioMovimiento:" + e.toString(), e);
        }
        finally {
            closeConnection(null, statement);
        }
    }
    
    private void insertCajaMovimiento(Connection con, CajaMovimientoDto dto) throws DAOException, CajaException {
        PreparedStatement statement = null;
        try {
            statement = con.prepareStatement("INSERT INTO " + SCHEMA + ".CAJA_MOVIMIENTO (CODIGO_CAJA, FECHA_MOVIMIENTO, CODIGO_ESTADO, UID_MOVIMIENTO) " +
                                             "VALUES (?, ?, ?, ?)");
            int indice = 0;
            statement.setObject(++indice, dto.getCodigoCaja());
            statement.setTimestamp(++indice, new Timestamp(dto.getFechaMovimiento().getTime()));
            statement.setObject(++indice, dto.getCodigoEstado());
            statement.setObject(++indice, dto.getUidMovimiento());
            statement.executeUpdate();
        } 
        catch (SQLException e) {
            throw new CajaException("DespachoDAO:insertCajaMovimiento:" + e.toString(), e);
        }
        finally {
            closeConnection(null, statement);
        }
    }
    
    public void cerrarDespacho(DespachoDto dto, List listCajas, String ldapUser) throws DAOException {
        Connection con = null;
        PreparedStatement statementQueryNodo = null;
        PreparedStatement statementQueryEnvios = null;
        PreparedStatement statementUpdateEnvio = null;
        PreparedStatement statementUpdateCaja = null;
        PreparedStatement statementUpdateDespachoBogota = null;
        PreparedStatement statementUpdateDespachoNoBogota = null;
        ResultSet rs = null;
        Date today = new Date();
        boolean isValidNodes = true;
        String codigoClasificacionGlobal = "";
        try {
            con = getConnectionDB2();
            con.setAutoCommit(false);
            statementQueryNodo = con.prepareStatement("SELECT CODIGO_CLASIFICACION " +
                                                      "FROM " + SCHEMA + ".NODO_DISTRIBUCION " +
                                                      "WHERE CODIGO_NODO = ?");
            statementQueryEnvios = con.prepareStatement("SELECT CODIGO_BARRAS, CODIGO_ULTIMA_GESTION " +
                                                        "FROM " + SCHEMA + ".ENVIO " +
                                                        "WHERE CODIGO_CAJA = ?");
            statementUpdateEnvio = con.prepareStatement("UPDATE " + SCHEMA + ".ENVIO " +
                                                        "SET CODIGO_ULTIMA_GESTION = ? " +
                                                        "WHERE CODIGO_BARRAS = ?");
            statementUpdateCaja = con.prepareStatement("UPDATE " + SCHEMA + ".CAJA " +
                                                       "SET CODIGO_ESTADO = ? " +
                                                       "WHERE CODIGO_CAJA = ?");
            statementUpdateDespachoBogota = con.prepareStatement("UPDATE " + SCHEMA + ".DESPACHO " +
                                                                 "SET CODIGO_PROVEEDOR_DESPACHADO = ?, CODIGO_ESTADO = ?, FECHA_DESPACHO = ? " +
                                                                 "WHERE CODIGO_DESPACHO = ?");
            statementUpdateDespachoNoBogota= con.prepareStatement("UPDATE " + SCHEMA + ".DESPACHO " +
                                                                  "SET NUMERO_DESPACHO = ?, CODIGO_PROVEEDOR_DESPACHADO = ?, CODIGO_ESTADO = ?, FECHA_DESPACHO = ? " +
                                                                  "WHERE CODIGO_DESPACHO = ?");
            int indice = 0;
            Iterator iterator = listCajas.iterator();
            while (iterator.hasNext()) {
                indice = 0;
                CajaDto cajaDto = (CajaDto) iterator.next();
                String codigoClasificacion = "";
                statementQueryNodo.setObject(++indice, cajaDto.getCodigoNodo());
                rs = statementQueryNodo.executeQuery();
                if (rs.next()) {
                    indice = 0;
                    codigoClasificacion = rs.getString(++indice);
                    codigoClasificacionGlobal = codigoClasificacionGlobal.equals("") ? codigoClasificacion : codigoClasificacionGlobal;
                }
                if ((codigoClasificacion.equals(ConstantesGenerales.CODIGO_CLASIFICACION_NORMAL) || codigoClasificacion.equals(ConstantesGenerales.CODIGO_CLASIFICACION_ES_NO_COMPUTEC) || codigoClasificacion.equals(ConstantesGenerales.CODIGO_CLASIFICACION_NO_REQUIERE_TRANSPORTE) || codigoClasificacion.equals(ConstantesGenerales.CODIGO_CLASIFICACION_EMPRESA_NO_CERTIFICADA)) && codigoClasificacion.equals(codigoClasificacionGlobal)) {
                    indice = 0;
                    statementQueryEnvios.setObject(++indice, cajaDto.getCodigoCaja());
                    rs = statementQueryEnvios.executeQuery();
                    while (rs.next()) {
                        indice = 0;
                        String codigoBarras = rs.getString(++indice);
                        String codigoUltimaGestion = rs.getString(++indice);
                        /**
                         * clasificacion nodo : normal 
                         * cambio de gestion envios: despacho - trasnporte
                         */
                        if (codigoUltimaGestion.equals(ConstantesGenerales.CODIGO_GESTION_ENVIO_DESPACHO) && codigoClasificacion.equals(ConstantesGenerales.CODIGO_CLASIFICACION_NORMAL)) {
                            indice = 0;
                            statementUpdateEnvio.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_TRANSPORTE);
                            statementUpdateEnvio.setObject(++indice, codigoBarras);
                            statementUpdateEnvio.executeUpdate();
                            EnvioMovimientoDto envioMovimientoDto = new EnvioMovimientoDto();
                            envioMovimientoDto.setCodigoBarras(codigoBarras);
                            envioMovimientoDto.setCodigoGestion(ConstantesGenerales.CODIGO_GESTION_ENVIO_TRANSPORTE);
                            envioMovimientoDto.setFechaMovimiento(today);
                            envioMovimientoDto.setFechaWeb(today);
                            envioMovimientoDto.setCodigoResponsable(ldapUser);
                            try {
                                this.insertEnvioMovimiento(con, envioMovimientoDto);
                            }
                            catch (DespachoException e) {
                                xLogger.error("DespachoDAO:cerrarDespacho:insertEnvioMovimiento:", e);
                            }
                        }
                        /**
                         * clasificacion nodo : es no computec 
                         * cambio de gestion envios: despacho - distribucion externa
                         */
                        else if (codigoUltimaGestion.equals(ConstantesGenerales.CODIGO_GESTION_ENVIO_DESPACHO) && codigoClasificacion.equals(ConstantesGenerales.CODIGO_CLASIFICACION_ES_NO_COMPUTEC)) {
                            indice = 0;
                            statementUpdateEnvio.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_DISTRIBUCION_EXTERNA);
                            statementUpdateEnvio.setObject(++indice, codigoBarras);
                            statementUpdateEnvio.executeUpdate();
                            EnvioMovimientoDto envioMovimientoDto = new EnvioMovimientoDto();
                            envioMovimientoDto.setCodigoBarras(codigoBarras);
                            envioMovimientoDto.setCodigoGestion(ConstantesGenerales.CODIGO_GESTION_ENVIO_DISTRIBUCION_EXTERNA);
                            envioMovimientoDto.setFechaMovimiento(today);
                            envioMovimientoDto.setFechaWeb(today);
                            envioMovimientoDto.setCodigoResponsable(ldapUser);
                            try {
                                this.insertEnvioMovimiento(con, envioMovimientoDto);
                            }
                            catch (DespachoException e) {
                                xLogger.error("DespachoDAO:cerrarDespacho:insertEnvioMovimiento:", e);
                            }
                        }
                        /**
                         * clasificacion nodo : no requiere trasnporte
                         * cambio de gestion envios: despacho - distribucion
                         */
                        else if (codigoUltimaGestion.equals(ConstantesGenerales.CODIGO_GESTION_ENVIO_DESPACHO) && codigoClasificacion.equals(ConstantesGenerales.CODIGO_CLASIFICACION_NO_REQUIERE_TRANSPORTE)) {
                            indice = 0;
                            statementUpdateEnvio.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_DISTRIBUCION);
                            statementUpdateEnvio.setObject(++indice, codigoBarras);
                            statementUpdateEnvio.executeUpdate();
                            EnvioMovimientoDto envioMovimientoDto = new EnvioMovimientoDto();
                            envioMovimientoDto.setCodigoBarras(codigoBarras);
                            envioMovimientoDto.setCodigoGestion(ConstantesGenerales.CODIGO_GESTION_ENVIO_DISTRIBUCION);
                            envioMovimientoDto.setFechaMovimiento(today);
                            envioMovimientoDto.setFechaWeb(today);
                            envioMovimientoDto.setCodigoResponsable(ldapUser);
                            try {
                                this.insertEnvioMovimiento(con, envioMovimientoDto);
                            }
                            catch (DespachoException e) {
                                xLogger.error("DespachoDAO:cerrarDespacho:insertEnvioMovimiento:", e);
                            }
                        }
                        /**
                         * clasificacion nodo : empresa no certificada
                         * cambio de gestion envios: despacho - entrega no certificada
                         */
                        else if (codigoUltimaGestion.equals(ConstantesGenerales.CODIGO_GESTION_ENVIO_DESPACHO) && codigoClasificacion.equals(ConstantesGenerales.CODIGO_CLASIFICACION_EMPRESA_NO_CERTIFICADA)) {
                            indice = 0;
                            statementUpdateEnvio.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_ENTREGA_NO_CERTIFICADA);
                            statementUpdateEnvio.setObject(++indice, codigoBarras);
                            statementUpdateEnvio.executeUpdate();
                            EnvioMovimientoDto envioMovimientoDto = new EnvioMovimientoDto();
                            envioMovimientoDto.setCodigoBarras(codigoBarras);
                            envioMovimientoDto.setCodigoGestion(ConstantesGenerales.CODIGO_GESTION_ENVIO_ENTREGA_NO_CERTIFICADA);
                            envioMovimientoDto.setFechaMovimiento(today);
                            envioMovimientoDto.setFechaWeb(today);
                            envioMovimientoDto.setCodigoResponsable(ldapUser);
                            try {
                                this.insertEnvioMovimiento(con, envioMovimientoDto);
                            }
                            catch (DespachoException e) {
                                xLogger.error("DespachoDAO:cerrarDespacho:insertEnvioMovimiento:", e);
                            }
                        }
                    }
                    if (codigoClasificacion.equals(ConstantesGenerales.CODIGO_CLASIFICACION_NORMAL)) {
                        indice = 0;
                        statementUpdateCaja.setObject(++indice, ConstantesGenerales.CODIGO_ESTADO_CAJA_EN_TRANSPORTE);
                        statementUpdateCaja.setObject(++indice, cajaDto.getCodigoCaja());
                        statementUpdateCaja.executeUpdate();
                        CajaMovimientoDto cajaMovimientoDto = new CajaMovimientoDto();
                        cajaMovimientoDto.setCodigoCaja(cajaDto.getCodigoCaja());
                        cajaMovimientoDto.setFechaMovimiento(today);
                        cajaMovimientoDto.setCodigoEstado(ConstantesGenerales.CODIGO_ESTADO_CAJA_EN_TRANSPORTE);
                        cajaMovimientoDto.setUidMovimiento(ldapUser);
                        this.insertCajaMovimiento(con, cajaMovimientoDto);
                    }
                    else {
                        indice = 0;
                        statementUpdateCaja.setObject(++indice, ConstantesGenerales.CODIGO_ESTADO_CAJA_RECIBIDA);
                        statementUpdateCaja.setObject(++indice, cajaDto.getCodigoCaja());
                        statementUpdateCaja.executeUpdate();
                        CajaMovimientoDto cajaMovimientoDto = new CajaMovimientoDto();
                        cajaMovimientoDto.setCodigoCaja(cajaDto.getCodigoCaja());
                        cajaMovimientoDto.setFechaMovimiento(today);
                        cajaMovimientoDto.setCodigoEstado(ConstantesGenerales.CODIGO_ESTADO_CAJA_RECIBIDA);
                        cajaMovimientoDto.setUidMovimiento(ldapUser);
                        this.insertCajaMovimiento(con, cajaMovimientoDto);
                    }
                }
                else {
                    isValidNodes = false;
                    break;
                }
            }
            if (isValidNodes && codigoClasificacionGlobal.equals(ConstantesGenerales.CODIGO_CLASIFICACION_NORMAL)) {
                indice = 0;
                if (dto.isBogota()) {
                    statementUpdateDespachoBogota.setObject(++indice, dto.getCodigoProveedorDespachado());
                    statementUpdateDespachoBogota.setObject(++indice, ConstantesGenerales.CODIGO_ESTADO_DESPACHO_EN_TRANSPORTE);
                    statementUpdateDespachoBogota.setTimestamp(++indice, new Timestamp(today.getTime()));
                    statementUpdateDespachoBogota.setObject(++indice, dto.getCodigoDespacho());
                    statementUpdateDespachoBogota.executeUpdate();
                }
                else {
                    statementUpdateDespachoNoBogota.setObject(++indice, dto.getNumeroDespacho());
                    statementUpdateDespachoNoBogota.setObject(++indice, dto.getCodigoProveedorDespachado());
                    statementUpdateDespachoNoBogota.setObject(++indice, ConstantesGenerales.CODIGO_ESTADO_DESPACHO_EN_TRANSPORTE);
                    statementUpdateDespachoNoBogota.setTimestamp(++indice, new Timestamp(today.getTime()));
                    statementUpdateDespachoNoBogota.setObject(++indice, dto.getCodigoDespacho());
                    statementUpdateDespachoNoBogota.executeUpdate();
                }
                DespachoMovimientoDto despachoMovimientoDto = new DespachoMovimientoDto();
                despachoMovimientoDto.setCodigoDespacho(dto.getCodigoDespacho());
                despachoMovimientoDto.setFechaMovimiento(today);
                despachoMovimientoDto.setCodigoEstado(ConstantesGenerales.CODIGO_ESTADO_DESPACHO_EN_TRANSPORTE);
                despachoMovimientoDto.setUidMovimiento(ldapUser);
                this.insertDespachoMovimiento(con, despachoMovimientoDto);
                con.commit();
            }
            else if (isValidNodes && !codigoClasificacionGlobal.equals(ConstantesGenerales.CODIGO_CLASIFICACION_NORMAL)) {
                indice = 0;
                if (dto.isBogota()) {
                    statementUpdateDespachoBogota.setObject(++indice, dto.getCodigoProveedorDespachado());
                    statementUpdateDespachoBogota.setObject(++indice, ConstantesGenerales.CODIGO_ESTADO_DESPACHO_ENTREGADO);
                    statementUpdateDespachoBogota.setTimestamp(++indice, new Timestamp(today.getTime()));
                    statementUpdateDespachoBogota.setObject(++indice, dto.getCodigoDespacho());
                    statementUpdateDespachoBogota.executeUpdate();
                }
                else {
                    statementUpdateDespachoNoBogota.setObject(++indice, dto.getNumeroDespacho());
                    statementUpdateDespachoNoBogota.setObject(++indice, dto.getCodigoProveedorDespachado());
                    statementUpdateDespachoNoBogota.setObject(++indice, ConstantesGenerales.CODIGO_ESTADO_DESPACHO_ENTREGADO);
                    statementUpdateDespachoNoBogota.setTimestamp(++indice, new Timestamp(today.getTime()));
                    statementUpdateDespachoNoBogota.setObject(++indice, dto.getCodigoDespacho());
                    statementUpdateDespachoNoBogota.executeUpdate();
                }
                DespachoMovimientoDto despachoMovimientoDto = new DespachoMovimientoDto();
                despachoMovimientoDto.setCodigoDespacho(dto.getCodigoDespacho());
                despachoMovimientoDto.setFechaMovimiento(today);
                despachoMovimientoDto.setCodigoEstado(ConstantesGenerales.CODIGO_ESTADO_DESPACHO_ENTREGADO);
                despachoMovimientoDto.setUidMovimiento(ldapUser);
                this.insertDespachoMovimiento(con, despachoMovimientoDto);
                con.commit();
            }
            else {
                try {
                    con.rollback();
                }
                catch (SQLException e) {
                    throw new DAOException("DespachoDAO:cerrarDespacho:rollback:" + e.toString(), e);
                }
            }
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:cerrarDespacho:" + e.toString(), e);
        }
        catch (CajaException e) {
            try {
                con.rollback();
                throw new DAOException("DespachoDAO:cerrarDespacho:" + e.toString(), e);
            }
            catch (SQLException ex) {
                throw new DAOException("DespachoDAO:cerrarDespacho:rollback:" + ex.toString(), ex);
            }
        }
        catch (DespachoException e) {
            try {
                con.rollback();
                throw new DAOException("DespachoDAO:cerrarDespacho:" + e.toString(), e);
            }
            catch (SQLException ex) {
                throw new DAOException("DespachoDAO:cerrarDespacho:rollback:" + ex.toString(), ex);
            }
        }
        catch (Exception e) {
            try {
                con.rollback();
                throw new DAOException("DespachoDAO:cerrarDespacho:" + e.toString(), e);
            }
            catch (SQLException ex) {
                throw new DAOException("DespachoDAO:cerrarDespacho:rollback:" + ex.toString(), ex);
            }
        }
        finally {
            closeConnection(null, statementQueryNodo);
            closeConnection(null, statementQueryEnvios, rs);
            closeConnection(null, statementUpdateEnvio);
            closeConnection(null, statementUpdateCaja);
            closeConnection(null, statementUpdateDespachoBogota);
            closeConnection(con, statementUpdateDespachoNoBogota);
        }
    }
    
    public List getRemision(DespachoDto dto) throws DAOException {
        List listDetalleCajasDespachadas = new ArrayList();
        Connection con = null;
        PreparedStatement statement = null;
        PreparedStatement statementFechaRecibida = null;
        PreparedStatement statementNumeroCajas = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        try {
            con = getConnectionDB2();
            con.setReadOnly(true);
            statementNumeroCajas = con.prepareStatement("SELECT CODIGO_CAJA " +
                                                        "FROM PRODRAD.CAJA " +
                                                        "WHERE CODIGO_DESPACHO = ?");
            int indice = 0;
            statementNumeroCajas.setObject(++indice, dto.getCodigoDespacho());
            rs2 = statementNumeroCajas.executeQuery();
            StringBuffer numcajasSB = new StringBuffer();
            while (rs2.next()) {
                int indice1 = 0;
                numcajasSB.append(",");
                numcajasSB.append(rs2.getString(++indice1));
            }
            statement = con.prepareStatement("SELECT E.CODIGO_CAJA, OP.NUMERO_ORDEN, ND.NOMBRE_NODO, CL.RAZON_SOCIAL, P.DESCRIPCION_PRODUCTO, OP.CICLO, E.CODIGO_BARRAS_INICIAL, E.CODIGO_BARRAS_FINAL, COUNT(E.CODIGO_BARRAS) " +
                                             "FROM PRODRAD.ENVIO E " +
                                             "INNER JOIN PRODRAD.ORDEN_PROCESO OP ON E.NUMERO_ORDEN = OP.NUMERO_ORDEN " +
                                             "INNER JOIN PRODRAD.NODO_DISTRIBUCION ND ON E.CODIGO_NODO = ND.CODIGO_NODO " +
                                             "INNER JOIN PRODRAD.CLIENTE CL ON E.CODIGO_CLIENTE = CL.CODIGO_CLIENTE " +
                                             "INNER JOIN PRODRAD.PRODUCTO P ON (E.CODIGO_CLIENTE = P.CODIGO_CLIENTE AND E.CODIGO_PRODUCTO = P.CODIGO_PRODUCTO) " +
                                             "WHERE E.CODIGO_CAJA IN (" + numcajasSB.toString().replaceFirst(",","") + ") " +
                                             "GROUP BY E.CODIGO_CAJA, OP.NUMERO_ORDEN, ND.NOMBRE_NODO, CL.RAZON_SOCIAL, P.DESCRIPCION_PRODUCTO, OP.CICLO, E.CODIGO_BARRAS_INICIAL, E.CODIGO_BARRAS_FINAL " +
                                             "ORDER BY E.CODIGO_CAJA DESC WITH UR");
            statementFechaRecibida = con.prepareStatement("SELECT FECHA_MOVIMIENTO " +
                                                          "FROM " + SCHEMA + ".CAJA_MOVIMIENTO " +
                                                          "WHERE CODIGO_CAJA = ? AND CODIGO_ESTADO = ?");
            rs = statement.executeQuery();
            while (rs.next()) {
                indice = 0;
                DetalleCajaDespachadaDto detalleCajaDespachadaDto = new DetalleCajaDespachadaDto();
                detalleCajaDespachadaDto.setCodigoCaja(rs.getString(++indice));
                detalleCajaDespachadaDto.setNumeroOrden(rs.getString(++indice));
                detalleCajaDespachadaDto.setNombreNodo(rs.getString(++indice));
                detalleCajaDespachadaDto.setRazonSocialCliente(rs.getString(++indice));
                detalleCajaDespachadaDto.setDescripcionProducto(rs.getString(++indice));
                detalleCajaDespachadaDto.setCiclo(rs.getString(++indice));
                detalleCajaDespachadaDto.setCodigoBarrasInicial(rs.getString(++indice));
                detalleCajaDespachadaDto.setCodigoBarrasFinal(rs.getString(++indice));
                detalleCajaDespachadaDto.setCantidad(rs.getString(++indice));
                indice = 0;
                statementFechaRecibida.setObject(++indice, detalleCajaDespachadaDto.getCodigoCaja());
                statementFechaRecibida.setObject(++indice, ConstantesGenerales.CODIGO_ESTADO_CAJA_RECIBIDA);
                rs1 = statementFechaRecibida.executeQuery();
                if (rs1.next()) {
                    indice = 0;
                    detalleCajaDespachadaDto.setFechaRecibida(new Date(rs1.getTimestamp(++indice).getTime()));
                    detalleCajaDespachadaDto.setFechaRecibidaFormat(ConstantesGenerales.SIMPLE_DATE_FORMAT.format(detalleCajaDespachadaDto.getFechaRecibida()));
                }
                listDetalleCajasDespachadas.add(detalleCajaDespachadaDto);
            }
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:getRemision:" + e.toString(), e);
        }
        finally {
            closeConnection(statementNumeroCajas, rs2);
            closeConnection(statement, rs);
            closeConnection(con, statementFechaRecibida, rs1);
        }
        return listDetalleCajasDespachadas;
    }
    
    public List getEnviosByOrdenProcesoPorDespachar(OrdenProcesoPorDespacharDto dto) throws DAOException {
        List listEnvios = new ArrayList();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = getConnectionDB2();
            con.setReadOnly(true);
            statement = con.prepareStatement("SELECT E.CONSECUTIVO_ENVIO, E.CODIGO_BARRAS, E.DIRECCION_DESTINATARIO, D.NOMBRE_DEPARTAMENTO, CP.NOMBRE_CENTRO_POBLADO, PV.RAZON_SOCIAL, ND.NOMBRE_NODO, E.NUMERO_ORDEN, TE.NOMBRE_TIPO_ENVIO " +
                                             "FROM " + SCHEMA + ".ENVIO E " +
                                             "INNER JOIN " + SCHEMA + ".NODO_DISTRIBUCION ND ON E.CODIGO_NODO = ND.CODIGO_NODO " +
                                             "INNER JOIN " + SCHEMA + ".DEPARTAMENTO D ON ND.CODIGO_DEPARTAMENTO = D.CODIGO_DEPARTAMENTO " +
                                             "INNER JOIN " + SCHEMA + ".MUNICIPIO M ON ND.CODIGO_DEPARTAMENTO = M.CODIGO_DEPARTAMENTO AND ND.CODIGO_MUNICIPIO = M.CODIGO_MUNICIPIO " +
                                             "INNER JOIN " + SCHEMA + ".CENTRO_POBLADO CP ON ND.CODIGO_DEPARTAMENTO = CP.CODIGO_DEPARTAMENTO AND ND.CODIGO_MUNICIPIO = CP.CODIGO_MUNICIPIO AND ND.CODIGO_CENTROP = CP.CODIGO_CENTROP " +
                                             "INNER JOIN " + SCHEMA + ".PROVEEDOR PV ON ND.CODIGO_PROVEEDOR = PV.CODIGO_PROVEEDOR " +
                                             "INNER JOIN " + SCHEMA + ".TIPO_ENVIO TE ON E.CODIGO_TIPO_ENVIO = TE.CODIGO_TIPO_ENVIO " +
                                             "WHERE E.NUMERO_ORDEN = ? AND E.CODIGO_CAJA IS NULL AND E.CODIGO_ULTIMA_GESTION IN (?, ?) AND ND.CODIGO_NODO = ? " +
                                             "ORDER BY E.CODIGO_BARRAS ASC WITH UR");
            int indice = 0;            
            statement.setObject(++indice, dto.getNumeroOrden());
            statement.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_DISTRIBUCION_EXTERNA);
            statement.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_DESPACHO);
            statement.setObject(++indice, dto.getCodigoNodo());
            rs = statement.executeQuery();
            while (rs.next()) {
                indice = 0;
                EnvioDto envioDto = new EnvioDto();
                envioDto.setConsecutivo(rs.getString(++indice));
                envioDto.setCodigoBarras(rs.getString(++indice));
                envioDto.setDireccionDestinatario(rs.getString(++indice));
                envioDto.setNombreDepartamento(rs.getString(++indice));
                envioDto.setNombreCentroPoblado(rs.getString(++indice));
                envioDto.setRazonSocialProveedor(rs.getString(++indice));
                envioDto.setNombreNodo(rs.getString(++indice));
                envioDto.setNumeroOrden(rs.getString(++indice));
                envioDto.setNombreTipoEnvio(rs.getString(++indice));
                listEnvios.add(envioDto);
            }
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:getEnviosByOrdenProcesoPorDespachar:" + e.toString(), e);
        }
        finally {
            closeConnection(con, statement, rs);
        }
        return listEnvios;
    }

    // <editor-fold defaultstate="collapsed" desc="method disabled: getNumeroEnviosPorDespachar()">
    /*public int getNumeroEnviosPorDespachar(DespachoDto dto, String daysBackStr) throws DAOException {
        int numeroEnviosPorDespachar = 0;
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        Date today = new Date();
        try {
            con = getConnectionDB2();
            con.setReadOnly(true);
            statement = con.prepareStatement("SELECT COUNT(E.CODIGO_BARRAS) " +
                                             "FROM " + SCHEMA + ".ENVIO E " +
                                             "INNER JOIN " + SCHEMA + ".ORDEN_PROCESO OP ON E.NUMERO_ORDEN = OP.NUMERO_ORDEN " +
                                             "INNER JOIN " + SCHEMA + ".NODO_DISTRIBUCION ND ON ND.CODIGO_PROVEEDOR = ? AND ND.CODIGO_DEPARTAMENTO = ? AND ND.CODIGO_MUNICIPIO = ? AND ND.CODIGO_CENTROP = ? AND E.CODIGO_NODO = ND.CODIGO_NODO " +
                                             "WHERE OP.FECHA_PROCESO BETWEEN ? AND ? AND E.CODIGO_CAJA IS NULL AND E.CODIGO_ULTIMA_GESTION IN (?, ?)");
            int indice = 0;
            int daysBack = Integer.valueOf(daysBackStr).intValue();
            Calendar calendarFechaInicio = Calendar.getInstance();
            calendarFechaInicio.setTime(today);
            calendarFechaInicio.add(Calendar.DATE, -daysBack);
            calendarFechaInicio.set(Calendar.HOUR_OF_DAY, 0);
            calendarFechaInicio.set(Calendar.MINUTE, 0);
            calendarFechaInicio.set(Calendar.SECOND, 0);
            calendarFechaInicio.set(Calendar.MILLISECOND, 0);
            statement.setObject(++indice, dto.getCodigoProveedor());
            statement.setObject(++indice, dto.getCodigoDepartamento());
            statement.setObject(++indice, dto.getCodigoMunicipio());
            statement.setObject(++indice, dto.getCodigoCentroPoblado());
            statement.setDate(++indice, new java.sql.Date(calendarFechaInicio.getTimeInMillis()));
            statement.setDate(++indice, new java.sql.Date(today.getTime()));
            statement.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_DESPACHO);
            statement.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_DISTRIBUCION_EXTERNA);
            rs = statement.executeQuery();
            if (rs.next()) {
                indice = 0;
                numeroEnviosPorDespachar = rs.getInt(++indice);
            }
        }
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:getNumeroEnviosPorDespachar:" + e.toString(), e);
        }
        finally {
            closeConnection(con, statement, rs);
        }
        return numeroEnviosPorDespachar;
    }*/
    // </editor-fold>
    
    public List getEnviosLoadGestionByOrdenProcesoPorDespachar(OrdenProcesoPorDespacharDto dto) throws DAOException {
        List listEnvios = new ArrayList();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = getConnectionDB2();
            con.setReadOnly(true);
            statement = con.prepareStatement("SELECT E.CODIGO_BARRAS " +
                                             "FROM PRODRAD.ENVIO E " +
                                             "INNER JOIN PRODRAD.NODO_DISTRIBUCION ND ON E.CODIGO_NODO = ND.CODIGO_NODO " +
                                             "WHERE E.NUMERO_ORDEN = ? AND E.CODIGO_CAJA IS NULL AND E.CODIGO_ULTIMA_GESTION IN (?, ?) AND ND.CODIGO_NODO = ? WITH UR");
            int indice = 0;            
            statement.setObject(++indice, dto.getNumeroOrden());
            statement.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_DISTRIBUCION_EXTERNA);
            statement.setObject(++indice, ConstantesGenerales.CODIGO_GESTION_ENVIO_DESPACHO);
            statement.setObject(++indice, dto.getCodigoNodo());
            rs = statement.executeQuery();
            while (rs.next()) {
                indice = 0;
                EnvioDto envioDto = new EnvioDto();
                envioDto.setCodigoBarras(rs.getString(++indice));
                listEnvios.add(envioDto);
            }
        } 
        catch (SQLException e) {
            throw new DAOException("DespachoDAO:getEnviosLoadGestionByOrdenProcesoPorDespachar:" + e.toString(), e);
        }
        finally {
            closeConnection(con, statement, rs);
        }
        return listEnvios;
    }
    
    public int calculateNroEnviosByDespachoByCaja() throws DAOException {
        Connection con = null;
        PreparedStatement statementQueryDespachos = null;
        PreparedStatement statementQueryCajas = null;
        PreparedStatement statementQueryEnvios = null;
        PreparedStatement statementUpdateCaja = null;
        PreparedStatement statementUpdateDespacho = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        ResultSet rs3 = null;
        final int indiceEstatico = 1;
        int numeroDespachosUpdate = 0;
        try {
            con = getConnectionDB2();
            con.setAutoCommit(false);
            statementQueryDespachos = con.prepareStatement("SELECT CODIGO_DESPACHO " +
                                                           "FROM " + SCHEMA + ".DESPACHO " +
                                                           "WHERE NRO_ENVIOS = ?");
            statementQueryCajas = con.prepareStatement("SELECT CODIGO_CAJA " +
                                                       "FROM " + SCHEMA + ".CAJA " +
                                                       "WHERE CODIGO_DESPACHO = ?");
            statementQueryEnvios = con.prepareStatement("SELECT COUNT(CODIGO_BARRAS) " +
                                                        "FROM " + SCHEMA + ".ENVIO " +
                                                        "WHERE CODIGO_CAJA = ?");
            statementUpdateCaja = con.prepareStatement("UPDATE " + SCHEMA + ".CAJA " +
                                                       "SET NRO_ENVIOS = ? " +
                                                       "WHERE CODIGO_CAJA = ?");
            statementUpdateDespacho = con.prepareStatement("UPDATE " + SCHEMA + ".DESPACHO " +
                                                           "SET NRO_ENVIOS = ? " +
                                                           "WHERE CODIGO_DESPACHO = ?");
            int indice = 0;
            statementQueryDespachos.setObject(indiceEstatico, 0);
            rs1 = statementQueryDespachos.executeQuery();
            while (rs1.next()) {
                String codigoDespacho = rs1.getString(indiceEstatico);
                int numeroEnviosDespacho = 0;
                statementQueryCajas.setObject(indiceEstatico, codigoDespacho);
                rs2 = statementQueryCajas.executeQuery();
                while (rs2.next()) {
                    String codigoCaja = rs2.getString(indiceEstatico);
                    statementQueryEnvios.setObject(indiceEstatico, codigoCaja);
                    rs3 = statementQueryEnvios.executeQuery();
                    if (rs3.next()) {
                        int numeroEnviosCaja = rs3.getInt(indiceEstatico);
                        if (numeroEnviosCaja > 0) {
                            indice = 0;
                            statementUpdateCaja.setObject(++indice, numeroEnviosCaja);
                            statementUpdateCaja.setObject(++indice, codigoCaja);
                            statementUpdateCaja.executeUpdate();
                            numeroEnviosDespacho += numeroEnviosCaja;
                        }
                    }
                }
                if (numeroEnviosDespacho > 0) {
                    indice = 0;
                    statementUpdateDespacho.setObject(++indice, numeroEnviosDespacho);
                    statementUpdateDespacho.setObject(++indice, codigoDespacho);
                    statementUpdateDespacho.executeUpdate();
                    numeroDespachosUpdate++;
                }
            }
            con.commit();
        } 
        catch (SQLException e) {
            try {
                con.rollback();
                throw new DAOException("DespachoDAO:calculateNroEnviosByDespachoByCaja:" + e.toString(), e);
            }
            catch (SQLException ex) {
                throw new DAOException("DespachoDAO:calculateNroEnviosByDespachoByCaja:rollback:" + ex.toString(), ex);
            }
        }
        catch (Exception e) {
            try {
                con.rollback();
                throw new DAOException("DespachoDAO:calculateNroEnviosByDespachoByCaja:" + e.toString(), e);
            }
            catch (SQLException ex) {
                throw new DAOException("DespachoDAO:calculateNroEnviosByDespachoByCaja:rollback:" + ex.toString(), ex);
            }
        }
        finally {
            closeConnection(statementQueryDespachos, rs1);
            closeConnection(statementQueryCajas, rs2);
            closeConnection(statementQueryEnvios, rs3);
            closeConnection(null, statementUpdateCaja);
            closeConnection(con, statementUpdateDespacho);
        }
        return numeroDespachosUpdate;
    }
}